﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Media.Core.Data
{
    public class Connections
    {
        // timeout in seconds for commands
        private const int KTimeout = 60;

        /// <summary>
        /// Gets a data reader from a SQL statement.
        /// </summary>
        /// <param name="sql">SQL Statement</param>
        /// <param name="connString">Connection String. Optional.</param>
        /// <returns>SQLDataReader</returns>
        public static SqlDataReader GetDataReader(string sql, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(sql, dbCon);
                command.CommandTimeout = 120;
                dbCon.Open();
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }
        }

        /// <summary>
        /// Gets single data row from a SQL statement.
        /// </summary>
        /// <param name="sql">SQL Statement</param>
        /// <param name="connString">Connection String. Optional.</param>
        /// <returns>DataRow containing the first row returned from the given query. Null returned if no rows returned.</returns>
        public static DataRow GetDataRow(string sql, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            DataTable newRow = new DataTable();
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(sql, dbCon);
                command.CommandTimeout = KTimeout;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                dbCon.Open();
                adapter.Fill(newRow);
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            if (newRow.Rows.Count > 0)
            {
                return newRow.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Gets single data row from a stored procedure.
        /// </summary>
        /// <param name="spCommandName">Stored procedure name.</param>
        /// <param name="paramCollection">System.Collection containing SQLParameters for the stored procedure.</param>
        /// <param name="connString">Connection String. Optional.</param>
        /// <returns>DataRow containing the first row returned from the given query. Null returned if no rows returned.</returns>
        public static DataRow GetDataRow(string spCommandName, ArrayList paramCollection, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            DataTable newRow = new DataTable();
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(spCommandName, dbCon);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = KTimeout;

                foreach (SqlParameter param in paramCollection)
                {
                    command.Parameters.Add(param);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                dbCon.Open();
                adapter.Fill(newRow);
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            if (newRow.Rows.Count > 0)
            {
                return newRow.Rows[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Gets a data table from a SQL statement.
        /// </summary>
        /// <param name="sql">SQL Statement</param>
        /// <param name="connString">Connection String. Optional.</param>
        /// <returns>DataRow containing the first row returned from the given query. Null returned if no rows returned.</returns>
        public static DataTable GetDataTable(string sql, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            DataTable newTable = new DataTable();
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(sql, dbCon);
                command.CommandTimeout = KTimeout;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                dbCon.Open();
                adapter.Fill(newTable);
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            return newTable;
        }

        /// <summary>
        /// Gets a data table from a stored procedure.
        /// </summary>
        /// <param name="spCommandName">Stored procedure name.</param>
        /// <param name="paramCollection">System.Collection containing SQLParameters for the stored procedure.</param>
        /// <param name="connString">Connection String. Optional.</param>
        /// <returns>DataTable containing the result returned from the given query.</returns>
        public static DataTable GetDataTable(string spCommandName, ArrayList paramCollection, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            DataTable newTable = new DataTable();
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(spCommandName, dbCon);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = KTimeout;

                foreach (SqlParameter param in paramCollection)
                {
                    command.Parameters.Add(param);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                dbCon.Open();
                adapter.Fill(newTable);
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            return newTable;
        }

        /// <summary>
        /// Gets a data set from a stored procedure.
        /// </summary>
        /// <param name="spCommandName">Stored procedure name.</param>
        /// <param name="paramCollection">System.Collection containing SQLParameters for the stored procedure.</param>
        /// <param name="connString">Connection String. Optional.</param>
        /// <param name="srcTable">Source Table. Optional.</param>
        /// <returns>DataSet containing the result returned from the given query.</returns>
        public static DataSet GetDataSet(string spCommandName, ArrayList paramCollection, string srcTable , string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            DataSet newSet = new DataSet();
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(spCommandName, dbCon);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = KTimeout;

                foreach (SqlParameter param in paramCollection)
                {
                    command.Parameters.Add(param);
                }
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                dbCon.Open();
                adapter.Fill(newSet, srcTable);
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            return newSet;
        }

        /// <summary>
        /// Gets a data set from a SQL query.
        /// </summary>
        /// <param name="sql">SQL Statement</param>
        /// <param name="connString">Connection String. Optional.</param>
        /// <param name="srcTable">Source Table. Optional.</param>
        /// <returns>DataSet containing the result returned from the given query.</returns>
        public static DataSet GetDataSet(string sql, string srcTable, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            DataSet newSet = new DataSet();
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(sql, dbCon);
                command.CommandType = CommandType.Text;
                command.CommandTimeout = KTimeout;
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                dbCon.Open();
                adapter.Fill(newSet, srcTable);
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            return newSet;
        }

        /// <summary>
        /// Execute a non returning stored procedure.
        /// </summary>
        /// <param name="spCommandName">Stored procedure name.</param>
        /// <param name="paramCollection">System.Collection containing SQLParameters for the stored procedure.</param>
        /// <param name="connString">Connection String. Optional.</param>
        /// <returns>The number of rows affected by this query.</returns>
        public static int ExecuteNonQuery(string spCommandName, ArrayList paramCollection, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            int retval = 0;
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(spCommandName, dbCon);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = KTimeout;

                foreach (SqlParameter param in paramCollection)
                {
                    command.Parameters.Add(param);
                }

                dbCon.Open();
                retval = command.ExecuteNonQuery();
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            return retval;
        }

        /// <summary>
        /// Execute a non returning SQL query.
        /// </summary>
        /// <param name="sql">SQL statement.</param>
        /// <param name="connString">Connection string. Optional.</param>
        /// <returns>The number of rows affected by this query.</returns>
        public static int ExecuteNonQuery(string sql, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            int retval = 0;
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(sql, dbCon);
                command.CommandTimeout = KTimeout;

                dbCon.Open();
                retval = command.ExecuteNonQuery();
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            return retval;
        }

        /// <summary>
        /// Executes a stored procedure returning a single value.
        /// </summary>
        /// <param name="spCommandName">Stored procedure.</param>
        /// <param name="paramCollection">System.Collection containing SQLParameters for the stored procedure.</param>
        /// <param name="connString">Connection string. Optional.</param>
        /// <returns>Scalar value returned by the stored procedure.</returns>
        public static object GetScalar(string spCommandName, ArrayList paramCollection, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            object result = null;
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(spCommandName, dbCon);
                command.CommandType = CommandType.StoredProcedure;
                command.CommandTimeout = KTimeout;

                foreach (SqlParameter param in paramCollection)
                {
                    command.Parameters.Add(param);
                }
                dbCon.Open();
                result = command.ExecuteScalar();
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            return result;
        }

        /// <summary>
        /// Executes a SQL query returning a single value.
        /// </summary>
        /// <param name="sql">SQL statement.</param>
        /// <param name="connString">Connection string. Optional.</param>
        /// <returns>Scalar value returned by the SQL query.</returns>
        public static object GetScalar(string sql, string connString = "MovieDbContext")
        {
            SqlConnection dbCon = null;
            object result = null;
            try
            {
                dbCon = new SqlConnection(ConfigurationManager.ConnectionStrings[connString].ConnectionString);
                SqlCommand command = new SqlCommand(sql, dbCon);
                command.CommandTimeout = KTimeout;
                dbCon.Open();
                result = command.ExecuteScalar();
            }
            finally
            {
                if (dbCon != null && dbCon.State != ConnectionState.Closed)
                {
                    dbCon.Close();
                }
            }

            return result;
        }



        public static bool IsDBNull(DataRow drInput, string columnName)
        {
            if (!drInput.Table.Columns.Contains(columnName))
            {
                return true;
            }
            if (drInput[columnName] == null)
            {
                return true;
            }
            if (drInput[columnName].GetType() == Type.GetType("System.DBNull"))
            {
                return true;
            }
            //else
            return false;
        }
    }
}